/**
 * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 * $Id: ConsultaDAO.java,v 1.10 
 * Universidad de los Andes (Bogotï¿½ - Colombia)
 * Departamento de Ingenierï¿½a de Sistemas y Computaciï¿½n 
 *
 * Ejercicio: TruequeAndes
 * Autor: Juan Diego Toro - 1-Marzo-2010
 * Ajustes : 2011
 * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 */
package co.edu.uniandes.truequeAndes.dao;

import java.io.File;
import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Properties;
import co.edu.uniandes.truequeAndes.vos.ValueRF1;
import co.edu.uniandes.truequeAndes.vos.ValueRF2;
import co.edu.uniandes.truequeAndes.vos.ValueRF3;
import co.edu.uniandes.truequeAndes.vos.ValueRF4;
import co.edu.uniandes.truequeAndes.vos.ValueRF6;
import co.edu.uniandes.truequeAndes.vos.ValueRF7;
import co.edu.uniandes.truequeAndes.vos.ValueRF8;
import co.edu.uniandes.truequeAndes.vos.ValueRF9;

/**
 * Clase ConsultaDAO, encargada de hacer las consultas bï¿½sicas para el cliente
 */
public class ConsultaDAO {

	//----------------------------------------------------
	//Constantes
	//----------------------------------------------------
	/**
	 * ruta donde se encuentra el archivo de conexiï¿½n.
	 */
	private static final String ARCHIVO_CONEXION = "/../conexion.properties";


	//----------------------------------------------------
	//Consultas
	//----------------------------------------------------


	//----------------------------------------------------
	//Atributos
	//----------------------------------------------------
	/**
	 * conexion con la base de datos
	 */
	public Connection conexion;

	/**
	 * nombre del usuario para conectarse a la base de datos.
	 */
	private String usuario="s2304a32";

	/**
	 * clave de conexiï¿½n a la base de datos.
	 */
	private String clave="awer123";

	/**
	 * URL al cual se debe conectar para acceder a la base de datos.
	 */
	private String cadenaConexion;

	/**
	 * constructor de la clase. No inicializa ningun atributo.
	 */
	public ConsultaDAO() 
	{  

	}

	// -------------------------------------------------
	// Mï¿½todos
	// -------------------------------------------------



	/**
	 * requerimiento 4
	 * 
	 */


	public Collection<ValueRF4> Requerimiento4() throws Exception
	{
		ArrayList<ValueRF4> retornar=new ArrayList<ValueRF4>();
		ValueRF4 valores=new ValueRF4();     
		PreparedStatement prepStmt = null;

		try
		{

			prepStmt = conexion.prepareStatement("select amostrar from (select PP.nombre, oo.id_producto_ofertado, oo.id_producto_demandado, level as nivelcito, sys_connect_by_path(nombre,'/') as amostrar from s2304a41.productos PP join s2304a41.ofertas_nociclicas OO on OO.ID_PRODUCTO_OFERTADO=PP.ID connect by nocycle prior OO.id_producto_demandado=OO.ID_PRODUCTO_OFERTADO)op join (select max(nivelcito) as nivel from (select PP.nombre, oo.id_producto_ofertado, oo.id_producto_demandado, level as nivelcito, sys_connect_by_path(nombre,'/') from s2304a41.productos PP join s2304a41.ofertas_nociclicas OO on OO.ID_PRODUCTO_OFERTADO=PP.ID connect by nocycle prior OO.id_producto_demandado=OO.ID_PRODUCTO_OFERTADO))gg on op.nivelcito=gg.nivel");
			ResultSet rs = prepStmt.executeQuery();



			while(rs.next())
			{
				String iterar=rs.getString("AMOSTRAR");
				String[] copiando=iterar.split("/");
				valores.setValor(copiando);
				retornar.add(valores);





				valores = new ValueRF4();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}

	/**
	 * obtiene ls datos necesarios para establecer una conexion
	 * Los datos se obtienen a partir de un archivo properties.
	 * @param path ruta donde se encuentra el archivo properties.
	 */
	public void inicializar()
	{
		try
		{
			//File arch= new File(path+ARCHIVO_CONEXION);
			//Properties prop = new Properties();
			//FileInputStream in = new FileInputStream( arch );

			//prop.load( in );
			//in.close( );

			cadenaConexion = "jdbc:oracle:thin:@chie.uniandes.edu.co:1521:chie10"; // El url, el usuario y passwd deben estar en un archivo de propiedades.
			// url: "jdbc:oracle:thin:@chie.uniandes.edu.co:1521:chie10";
			usuario = "s2304a32"; // "s2501aXX";
			clave = "awer123" ;// "c2501XX";

			final String driver = "oracle.jdbc.driver.OracleDriver";
			System.out.println(driver);

			Class.forName(driver);
			establecerConexion(cadenaConexion, usuario, clave);


		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
	}

	/**
	 * Mï¿½todo que se encarga de crear la conexiï¿½n con el Driver Manager
	 * a partir de los parametros recibidos.
	 * @param url direccion url de la base de datos a la cual se desea conectar
	 * @param usuario nombre del usuario que se va a conectar a la base de datos
	 * @param clave clave de acceso a la base de datos
	 * @throws SQLException si ocurre un error generando la conexiï¿½n con la base de datos.
	 */
	private void establecerConexion(String url, String usuario, String clave) throws SQLException
	{
		try
		{
			conexion = DriverManager.getConnection(url,usuario,clave);
			System.out.println(conexion);
		}
		catch( SQLException exception )
		{
			System.out.println(exception.getCause());
			throw new SQLException( "ERROR: ConsultaDAO obteniendo una conexiï¿½n." );
		}
	}

	/**
	 *Cierra la conexiï¿½n activa a la base de datos. Ademï¿½s, con=null.
	 * @param con objeto de conexiï¿½n a la base de datos
	 * @throws SistemaCinesException Si se presentan errores de conexiï¿½n
	 */
	public void closeConnection(Connection connection) throws Exception {        
		try {
			connection.close();
			connection = null;
		} catch (SQLException exception) {
			throw new Exception("ERROR: ConsultaDAO: closeConnection() = cerrando una conexiï¿½n.");
		}
	} 

	// ---------------------------------------------------
	// Mï¿½todos asociados a los requerimientos Funcionales de  Consulta
	// ---------------------------------------------------


	/**
	 * Mï¿½todo que se encarga de realizar la consulta en la base de datos
	 * y retorna un elemento de tipo ClientesValue.
	 * @return ClienteValue cliente con el mayor nï¿½mero de productos ofertados en TruequeAndes. 
	 * @throws Exception se lanza una excepciï¿½n si ocurre un error en
	 * la conexiï¿½n o en la consulta. 
	 */
	public Collection<ValueRF1> darCantidadProductosPorCategoria() throws Exception
	{
		ArrayList<ValueRF1> retornar=new ArrayList<ValueRF1>();
		ValueRF1 valores=new ValueRF1();

		PreparedStatement prepStmt = null;

		try
		{
			prepStmt = conexion.prepareStatement("select C.nombre, count(OP.categoria) as cantidad from S2304A41.categorias C left join (select * from productos join ofertas on productos.id=ofertas.id_producto_ofertado)OP  on c.id=categoria group by C.nombre");
			ResultSet rs = prepStmt.executeQuery();


			while(rs.next())
			{
				String nombreCategoria = rs.getString("NOMBRE");
				int cantidad = Integer.parseInt(rs.getString("CANTIDAD"));


				valores.setNombre(nombreCategoria);
				valores.setCantidad(cantidad);
				retornar.add(valores);



				valores = new ValueRF1();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}



	public Collection<ValueRF2> Requerimiento2(String nCategoria, boolean orden, String organizado) throws Exception
	{
		ArrayList<ValueRF2> retornar=new ArrayList<ValueRF2>();
		ValueRF2 valores=new ValueRF2();

		PreparedStatement prepStmt = null;

		try
		{
			String gg;
			if(orden)
				gg="asc";
			else
				gg="desc";
			prepStmt = conexion.prepareStatement("select nombreproducto, cantidad, productos.valor_estimado from productos join (select nombreproducto, cantidad from (select * from s2304a41.categorias left join (select count(*) as cantidad, nombre as nombreProducto, categoria from (select productos.nombre, productos.categoria from productos join ofertas on productos.id=ofertas.id_producto_ofertado) group by nombre, categoria)on categoria=categorias.id ) where nombre=' " + nCategoria + "' order by " + organizado +" " +  gg + ") on productos.nombre=nombreproducto");
			ResultSet rs = prepStmt.executeQuery();




			while(rs.next())
			{

				String nombreProducto = rs.getString("NOMBREPRODUCTO");
				int cantidad = Integer.parseInt(rs.getString("CANTIDAD"));
				int valorEstimado = Integer.parseInt(rs.getString("VALOR_ESTIMADO"));


				valores.setNombre(nombreProducto);
				valores.setCantidad(cantidad);
				valores.setValor(valorEstimado);
				retornar.add(valores);

				valores = new ValueRF2();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}





	/**
	 * rf3
	 */

	public Collection<ValueRF3> Requerimiento3(String busqueda, boolean orden, String organizado) throws Exception
	{
		ArrayList<ValueRF3> retornar=new ArrayList<ValueRF3>();
		ValueRF3 valores=new ValueRF3();

		PreparedStatement prepStmt = null;

		try
		{
			String gg;
			if(orden)
				gg="asc";
			else
				gg="desc";
			prepStmt = conexion.prepareStatement("select nombre_producto, cantidad, valor_estimado from productos join (select productos.nombre as nombre_producto, count(*) as cantidad from productos join ofertas on productos.id=ofertas.id_producto_ofertado group by productos.nombre) on productos.nombre=nombre_producto where nombre_producto LIKE '%"+busqueda+"%' order by "+ organizado + " " + gg );
			ResultSet rs = prepStmt.executeQuery();




			while(rs.next())
			{

				String nombreProducto = rs.getString("NOMBRE_PRODUCTO");
				int cantidad = Integer.parseInt(rs.getString("CANTIDAD"));
				int valorEstimado = Integer.parseInt(rs.getString("VALOR_ESTIMADO"));


				valores.setNombre(nombreProducto);
				valores.setCantidad(cantidad);
				valores.setValor(valorEstimado);
				retornar.add(valores);

				valores = new ValueRF3();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}





	/**
	 * rf6
	 */

	public Collection<ValueRF6> Requerimiento6() throws Exception
	{
		ArrayList<ValueRF6> retornar=new ArrayList<ValueRF6>();
		ValueRF6 valores=new ValueRF6();

		PreparedStatement prepStmt = null;

		try
		{

			prepStmt = conexion.prepareStatement("select * from clientes join (select *  from  (Select count(*) as cantidad, email_cliente from (select * from productos join ofertas on productos.id=ofertas.id_producto_ofertado)  group by email_cliente order by cantidad desc) where rownum='1') on email_cliente=clientes.email ");
			ResultSet rs = prepStmt.executeQuery();




			while(rs.next())
			{
				String email=rs.getString("EMAIL");
				String nickname = rs.getString("NICKNAME");
				String pais =rs.getString("PAIS");
				String nombre=rs.getString("NOMBRE");
				int cantidad=Integer.parseInt(rs.getString("CANTIDAD"));


				valores.setCantidad(cantidad);
				valores.setEmail(email);
				valores.setNickname(nickname);
				valores.setPais(pais);
				valores.setNombreCliente(nombre);
				retornar.add(valores);



				valores = new ValueRF6();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}






	/**
	 * rf7
	 */


	public Collection<ValueRF7> Requerimiento7() throws Exception
	{
		ArrayList<ValueRF7> retornar=new ArrayList<ValueRF7>();
		ValueRF7 valores=new ValueRF7();

		PreparedStatement prepStmt = null;

		try
		{

			prepStmt = conexion.prepareStatement("select p.nombre, client.nombre as Nombre_Cliente, cat.nombre as Nombre_Categoria from ofertas o,clientes client, categorias cat, productos p,(select min (o.fecha) as minimo from ofertas o )l where o.fecha=l.minimo and cat.id=p.categoria and p.id=o.id_producto_ofertado and p.email_cliente= client.email ");
			ResultSet rs = prepStmt.executeQuery();



			while(rs.next())
			{
				String nombre=rs.getString("NOMBRE");
				String nombreCliente=rs.getString("NOMBRE_CLIENTE");
				String nombreCategoria=rs.getString("NOMBRE_CATEGORIA");

				valores.setNombreCategoria(nombreCategoria);
				valores.setNombreCliente(nombreCliente);
				valores.setNombreProducto(nombre);
				retornar.add(valores);




				valores = new ValueRF7();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}




	/**
	 * rf8
	 */



	public Collection<ValueRF8> Requerimiento8(String fecha1, String fecha2) throws Exception
	{
		ArrayList<ValueRF8> retornar=new ArrayList<ValueRF8>();
		ValueRF8 valores=new ValueRF8();

		PreparedStatement prepStmt = null;

		try
		{

			prepStmt = conexion.prepareStatement("select categorias.nombre, FT.nombre, FT.cantidad  from categorias join (select *  from (select OP.*, ofertas.fecha from ofertas join  (select productos.categoria, GG.nombre, id, GG.cantidad from productos join (select nombre, count(*) as cantidad from (select * from productos join ofertas on ofertas.id_producto_ofertado=productos.id) group by nombre)GG on GG.nombre=productos.nombre)OP on OP.id=ofertas.id_producto_ofertado) OV   where OV.fecha>' " + fecha1 + "' and OV.fecha < '" + fecha2 + "' order by nombre) FT on categoria=categorias.id");
			ResultSet rs = prepStmt.executeQuery();



			while(rs.next())
			{
				String nombreProducto=rs.getString("NOMBREPRODUCTO");
				String nombre=rs.getString("NOMBRE");
				int cantidad=Integer.parseInt(rs.getString("CANTIDAD"));

				valores.setCantidad(cantidad);
				valores.setCategoria(nombre);
				valores.setProducto(nombreProducto);
				retornar.add(valores);


				valores = new ValueRF8();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}


	/**
	 * rf9
	 */

	public Collection<ValueRF9> Requerimiento8() throws Exception
	{
		ArrayList<ValueRF9> retornar=new ArrayList<ValueRF9>();
		ValueRF9 valores=new ValueRF9();

		PreparedStatement prepStmt = null;

		try
		{

			prepStmt = conexion.prepareStatement("select nombre, maxima,dia from categorias join (select OP.maxima, OP.dia, OP.categoria from (select max(cantidad) as maxima, dia, categoria from (select count(*) as cantidad,dia, categoria from (select  categoria, extract(day from fecha) as dia from productos join ofertas on productos.id=ofertas.id_producto_ofertado) group by dia, categoria) group by categoria, dia)OP join (select max(cantidad) as maxima, categoria from (select count(*) as cantidad,dia, categoria from (select  categoria, extract(day from fecha) as dia from productos join ofertas on productos.id=ofertas.id_producto_ofertado) group by dia, categoria) group by categoria)GG on OP.categoria=GG.categoria and OP.maxima=GG.maxima) on categorias.id=categoria ");
			ResultSet rs = prepStmt.executeQuery();



			while(rs.next())
			{
				String nombre=rs.getString("NOMBRE");
				int maxima=Integer.parseInt(rs.getString("MAXIMA"));
				int dia=Integer.parseInt(rs.getString("DIA"));

				valores.setDia(dia);
				valores.setCantidad(maxima);
				valores.setNombre(nombre);
				retornar.add(valores);




				valores = new ValueRF9();

			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		finally {
			if (prepStmt != null) {
				try {
					prepStmt.close();
				} catch (SQLException exception) {

					exception.printStackTrace();
				}
			}

		}  
		return retornar;




	}

	public Connection darConexion ()
	{
		return conexion;
	}




}